﻿using System;
using System.Data;



/// <summary>
/// Summary description for DirectServiceSupplier
/// </summary>
public class DirectServiceSupplier
{
    private ConnectionBase sgbdr;
    public DirectServiceSupplier()
    {
      sgbdr= new ConnectionBase();
    }
    public DataTable GetSupplierDisplayeds(string whereclause, string orderclause)
    {
        string sql = "select * from COMPANYCONCERNING";
        sql += whereclause + " " + "Order by COMPANYCONCERNING.CAMPANYNAME" ;
        return sgbdr.consultSQL(sql).Tables[0];
    }
   
    public DataTable GetSupplierDisplayed(string whereclause,string orderclause)
    {

        //string sql = "select CompanyId,CompanyName,Phone,Fax,Email,Vat,CONVERT(decimal(8, 2), Vat) from COMPANYCONCERNING inner join TCategories  on COMPANYCONCERNING.CategoryId=TCategories.CategoryId";
        string sql="select * from COMPANYCONCERNING inner join TCategories  on COMPANYCONCERNING.CategoryId=TCategories.CategoryId";
        sql += whereclause + " " + "Order by COMPANYCONCERNING.COMPANYNAME";
        return sgbdr.consultSQL(sql).Tables[0];

    }
    
    public void UpdateSupplier(int companyId ,String companyName,int categoryId,float vat,string fax,string phone,string email)
    {
        string sgl = "update COMPANYCONCERNING set CompanyName=" + "'" + companyName + "'" +",CategoryId=" + categoryId  + ",Vat=" + vat +
                     ",Fax=" + "'" + fax + "'" + ",Phone=" + "'" + phone + "'" + ",Email=" +"'" + email + "'" + "where CompanyId=" + companyId ;
        sgbdr.executeSQL(sgl);
    }
    public void DeteteSupplier(int companyId)
    {
        string sql = "delete From COMPANYCONCERNING where CompanyId =" + companyId;
        sgbdr.executeSQL(sql);
    }

    public DataTable GetContactBySupplierId(string supplierId)
    {
        if (!String.IsNullOrEmpty(supplierId))
        {
            string sql = "select * from TContacts where ContactId in (select ContactId from TSupplierContacts where SupplierId=" +
                         supplierId + ")";
            return sgbdr.consultSQL(sql).Tables[0];
        }
        return null;
    }
    public DataTable GetEmployeeEquipmentDisplayedByEmployeeId(string employeeId)
    {
        if(!string.IsNullOrEmpty(employeeId))
        {
            string sql=" SELECT EQUIPEMENT.CODENUMBER, HAVE.typeEquipementID, HAVE.systemID, SYSTEM.SYSTEM, TYPEEQUIPMENT.TYPEEQUIPMENT, model.model,"
				+ " Convert(varchar,EQUIPMENTPURCHASEINFORMATION.PURCHASEDATE,101) AS PURCHASEDATE, EQUIPMENTPURCHASEINFORMATION.ISWARRANTY"
				+ " FROM EQUIPEMENT INNER JOIN"
				+ " EQUIPMENTPURCHASEINFORMATION ON EQUIPEMENT.PURCHASEID = EQUIPMENTPURCHASEINFORMATION.PURCHASEID INNER JOIN"
				+ " model on model.modelid = equipement.modelid inner join"
				+ " HAVE ON EQUIPMENTPURCHASEINFORMATION.haveID = HAVE.haveID INNER JOIN"
				+ " SYSTEM ON HAVE.systemID = SYSTEM.SYSTEMID INNER JOIN"
				+ " TYPEEQUIPMENT ON HAVE.typeEquipementID = TYPEEQUIPMENT.TYPEEQUIPEMENTID"
				+ " WHERE (EQUIPEMENT.CODENUMBER IN(SELECT CODENUMBER FROM OWN WHERE(unuse = 0) AND (EMPLOYEEID = "+employeeId+")))";
            return sgbdr.consultSQL(sql).Tables[0]; 
        }
        return null;
    }
    public DataTable GetSpartartByCodeNumber(string codeNumber) //function on form employee
    {

        if (!string.IsNullOrEmpty(codeNumber))
        {
            string sql = "SELECT SPAREPART.SPAREPARTID, MODEL.MODEL, CONVERT(VARCHAR,SPAREPARTPURCHASEINFORMATION.PURCHASEDATE,101) AS PURCHASEDATE,"
                    + " SPAREPARTPURCHASEINFORMATION.ISWARRANTY, SYSTEM.SYSTEM, TYPEEQUIPMENT.TYPEEQUIPMENT, CONVERT(VARCHAR,COMPOSE.DATECREATION,101) AS DATECREATION"
                    + " FROM SPAREPART INNER JOIN"
                    + " SPAREPARTPURCHASEINFORMATION ON SPAREPART.INFORMATIONID = SPAREPARTPURCHASEINFORMATION.INFORMATIONID INNER JOIN"
                    + " HAVE ON SPAREPARTPURCHASEINFORMATION.haveID = HAVE.haveID INNER JOIN"
                    + " TYPEEQUIPMENT ON HAVE.typeEquipementID = TYPEEQUIPMENT.TYPEEQUIPEMENTID INNER JOIN"
                    + " SYSTEM ON HAVE.systemID = SYSTEM.SYSTEMID INNER JOIN"
                    + " MODEL ON SPAREPART.MODELID = MODEL.MODELID INNER JOIN"
                    + " COMPOSE ON SPAREPART.SPAREPARTID = COMPOSE.SPAREPARTID"
                    + " WHERE (COMPOSE.CODENUMBER = '" + codeNumber + "')";
            return sgbdr.consultSQL(sql).Tables[0];
        }
        return null;
    }
   
    public void UpdateContacts(int contactId, string phone, string fax, string position,string email)
    {

        string sql = "update TContacts set Phone=" + "'" + phone + "'" +
        "," + "Fax=" + "'" + fax + "'" + "," + "Position=" + "'" + position + "'" + "," + "Email=" + "'" + email + "'" + "where ContactId=" + contactId ;
        sgbdr.executeSQL(sql);
       
    }
    public void GetSystemByCodeNumber(int systemId ) // it is function in Owner 
    {
        

    }

    public void DeleteContacts(int contactId)
    {

        string sql = "delete from TContacts where ContactId=" + contactId;
        sgbdr.executeSQL(sql);

    }
    public DataTable GetOwnerByCodeNumber(string codeNumber)
    {
        if(!string.IsNullOrEmpty(codeNumber))
        {
            string sql = "select * from OWN where CODENUMBER=" + codeNumber;
            return sgbdr.consultSQL(sql).Tables[0];

        }
        return null;
        
    }




}
